<?php
include_once("Classes/PHPExcel.php");

function connGet(){
	$DBADD = 'localhost:3306';
	$DBUSER = 'root';
	$DBPASS = '123456';
	$DBNAME = 'cash2uca';
	
	$conn = mysql_pconnect($DBADD, $DBUSER, $DBPASS);
	if ($conn == false){
		return false;
	}
	
	if (mysql_select_db($DBNAME, $conn) == false){
		return false;
	}
	
	return $conn;
}

function connClose($conn){
	mysql_close($conn);
}

/*
$results is result set from querying db
*/
function writeExcel($resource, $fileName){
	$excel = new PHPExcel();
	$excel->createSheet();
	$excel->setActiveSheetIndex(0);
	$lineNum = 1;
	
	while ($row = mysql_fetch_array($resource)){
		for ($i = 0; $i < count($row)/2; $i++){
			$excel->getActiveSheet()->setCellValue(number2Letter($i+1,1).$lineNum,$row[$i]);
		}
		
		$lineNum++;
	}
	
	$writer = PHPExcel_IOFactory::createWriter($excel,"Excel2007");
	$writer->save($fileName);
}

function number2Letter($num, $round){
	$letters = array(
		'1' => 'A',
		'2' => 'B',
		'3' => 'C',
		'4' => 'D',
		'5' => 'E',
		'6' => 'F',
		'7' => 'G',
		'8' => 'H',
		'9' => 'I',
		'10' => 'G',
		'11' => 'K',
		'12' => 'L',
		'13' => 'M',
		'14' => 'N',
		'15' => 'O',
		'16' => 'P',
		'17' => 'Q',
		'18' => 'R',
		'19' => 'S',
		'20' => 'T',
		'21' => 'U',
		'22' => 'V',
		'23' => 'W',
		'24' => 'X',
		'25' => 'Y',
		'26' => 'Z'
	);
	
	if ($num > 26){
		if (intval($num / 26) > 26){
			number2Letter(intval($num / 26), $round++);
		}
		else {
			if ($num % 26 != 0){
				return $letters[intval($num / 26)].$letters[$num % 26];
			}
			else {
				return $letters[intval($num / 26)].'Z';
			}
		}
	}
	else {
		return $letters[$num];
	}
}

function main(){
	$conn = connGet();
	if ($conn == false){
		return false;
	}

	// check duplicated email
	$query = "select * from profiles limit 100";
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		print('profiles retrieving failed.');
		exit();
	}
	
	$filePath = writeExcel($resource,'first1.xlsx');
	//downloadExcel($filePath);ssss
}

main();
?>